import re  
from openpyxl import load_workbook  
from collections import defaultdict
  
file_path = './开票明细.xlsx'  
   
workbook = load_workbook(file_path)  
sheet0 = workbook['Sheet0']

sheet_names = workbook.sheetnames 
if 'Sheet1' in sheet_names:
    sheet = workbook['Sheet1']
    workbook.remove(sheet)
    workbook.save(file_path)
    
sheet1 = workbook.create_sheet('Sheet1')  
  
sheet1['A1'] = '材料类型'  
sheet1['B1'] = '物品名称'  
sheet1['C1'] = '数量'
sheet1['D1'] = '含税单价'  
  
# 正则表达式模式  
#pattern = re.compile(r'(?:\**?[\d.]+(?:[*xX×][\d.]+){1,2}[A-Za-z]*)')
#pattern = re.compile(r'(?:\**?[\d.]+(?:[*xX×][\d.]+)*)(?:[*xX][Cc])?')
#pattern = re.compile(r'(?:\**?[\d.]+(?:[*xX×][\d.]+){1,2}[A-Za-z]*)')
pattern = re.compile(r'(?:\**?[\d.]+(?:[*xX×][\d.]+)*)(?:[*xX][*CcLl])?')  

for row_idx in range(2, sheet0.max_row + 1):  
    cell_value_d = sheet0.cell(row=row_idx, column=4).value  # D列数据  
    cell_value_e = sheet0.cell(row=row_idx, column=5).value  # E列数据  
    cell_value_n = sheet0.cell(row=row_idx, column=14).value  # N列数据（注意列索引从1开始，N列是第14列）  
    cell_str_d = str(cell_value_d)  
      
    match = pattern.search(cell_str_d)
    #print(match.group())  
    material_type = ''  
    if '镀锌' in cell_str_d.lower():  
        material_type = '镀锌板'  
    elif '冷轧' in cell_str_d.lower():
        material_type = '冷轧板'  
    elif '冷扎' in cell_str_d.lower():
        material_type = '冷轧板'
    elif 'spcc' in cell_str_d.lower():
        material_type = '冷轧板'

    use_text=match.group()
    use_text=use_text.lstrip("*").replace("x","*").replace("X","*").replace("×","*")
    try:
        use_text2=use_text.split('*')
        if len(use_text2)<3:
            print(use_text," 数据有误，请检查！")
            sheet1.cell(row=row_idx, column=5).value = "该行数据可能错误"
        elif len(use_text2)>3:
            #print(use_text2)
            try:
                test=float(use_text2[0])
                if test<0.1 or test>3.0:
                    del use_text2[0]
                    use_text = '*'.join(use_text2)
                else:
                    print(use_text," 数据有误，请检查！")
                    sheet1.cell(row=row_idx, column=5).value = "该行数据可能错误"
            except:
                del use_text2[0]
                use_text = '*'.join(use_text2)
            
            #print(use_text2)
            #print(use_text)
    except:
        print(use_text," 数据有误，请检查！")
        sheet1.cell(row=row_idx, column=5).value = "该行数据可能错误"
    try:
        use_text2=use_text.split('*')
        if "C" not in use_text2 and "c" not in use_text2:
            #print(use_text2)
            def change_num(use_text2):
                use_text2[1], use_text2[2] = use_text2[2], use_text2[1]
                use_text2 = '*'.join(use_text2)
                return use_text2
            try:
                if(int(use_text2[1]) > int(use_text2[2])):
                    use_text2=change_num(use_text2)
                    use_text=use_text2
            except:
                if(float(use_text2[1]) > float(use_text2[2])):
                    use_text2=change_num(use_text2)
                    use_text=use_text2
    except:
        ''
        
    # 写入sheet1  
    if match:  
        sheet1.cell(row=row_idx, column=2).value =  use_text # B列写入匹配项
    if material_type:  
        sheet1.cell(row=row_idx, column=1).value = material_type  # A列写入材料类型  
    sheet1.cell(row=row_idx, column=3).value = cell_value_e  # C列写入E列数据  
    sheet1.cell(row=row_idx, column=4).value = cell_value_n  # D列写入N列数据
  
# 保存工作簿  
workbook.save(file_path)


#############


workbook = load_workbook(filename=file_path)  
sheet = workbook['Sheet1']
 
merged_data = defaultdict(int)  
    
for row in sheet.iter_rows(min_row=2, values_only=True):
    key = (row[0], row[1], row[3] if len(row) > 3 else None)  
    value = row[2] if len(row) > 2 and row[2] is not None else 0  
      
    if isinstance(value, (int, float)):  
        merged_data[key] += value  
 
if sheet.max_row > 1:  
    for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row, max_col=sheet.max_column):  
        for cell in row:  
            cell.value = None  
   
row_num = 2  
for key, value in merged_data.items():  
    sheet.cell(row=row_num, column=1, value=key[0])  
    sheet.cell(row=row_num, column=2, value=key[1])  
    sheet.cell(row=row_num, column=3, value=value)
    sheet.cell(row=row_num, column=4, value=key[2] if len(key) > 2 else None)
    row_num += 1  
  
workbook.save(filename=file_path)

print("修改完成！")


input("按Enter或者关闭窗口退出...")



















